use chrono::NaiveDateTime;
use sea_orm::{
    ActiveModelTrait, ColumnTrait, DatabaseConnection, DbErr, EntityTrait, JoinType,
    PaginatorTrait, QueryFilter, QueryOrder, QuerySelect, RelationTrait,
};
use sky_common::constant::{self, status};
use sky_pojo::{
    entities::{self, order_detail, orders, user},
    vo::report::GoodsSalesDTO,
};

pub async fn ammount_days(
    db: &DatabaseConnection,
    begin: NaiveDateTime,
    end: NaiveDateTime,
) -> Result<Option<f64>, DbErr> {
    let result = orders::Entity::find()
        .select_only()
        .column_as(entities::orders::Column::Amount.sum(), "sum_of_amount")
        .filter(entities::orders::Column::Status.eq(constant::order::COMPLETED))
        .filter(entities::orders::Column::OrderTime.gte(begin))
        .filter(entities::orders::Column::OrderTime.lte(end))
        .into_json()
        .one(db)
        .await;

    if let Ok(Some(row)) = result {
        if let Some(sum) = row.get("sum_of_amount") {
            Ok(sum.as_f64()) // 返回总金额
        } else {
            Ok(Some(0.0)) // 如果查询为空，返回0.0
        }
    } else {
        Ok(Some(0.0)) // 没有匹配的行时，返回0.0
    }
}

pub async fn get_user_count(
    db: &DatabaseConnection,
    begin: Option<NaiveDateTime>,
    end: Option<NaiveDateTime>,
) -> Result<Option<i64>, DbErr> {
    let mut stmt = entities::user::Entity::find()
        .select_only()
        .column_as(user::Column::Id.count(), "sum_of_user");

    if begin.is_some() {
        stmt = stmt.filter(user::Column::CreateTime.gte(begin));
    }

    if end.is_some() {
        stmt = stmt.filter(user::Column::CreateTime.gte(end));
    }

    let result = stmt.into_json().one(db).await;

    if let Ok(Some(row)) = result {
        if let Some(sum) = row.get("sum_of_user") {
            Ok(sum.as_i64())
        } else {
            Ok(Some(0))
        }
    } else {
        Ok(Some(0))
    }
}

pub async fn get_order_count(
    db: &DatabaseConnection,
    begin: NaiveDateTime,
    end: NaiveDateTime,
    status: Option<i32>,
) -> Result<Option<i64>, DbErr> {
    let mut stmt = entities::orders::Entity::find()
        .select_only()
        .column_as(entities::orders::Column::Id.count(), "order_count");

    if status.is_some() {
        stmt = stmt.filter(entities::orders::Column::Status.eq(status));
    }

    let result = stmt
        .filter(entities::orders::Column::OrderTime.gte(begin))
        .filter(entities::orders::Column::OrderTime.gte(end))
        .into_json()
        .one(db)
        .await;

    if let Ok(Some(row)) = result {
        if let Some(sum) = row.get("sum_of_user") {
            Ok(sum.as_i64())
        } else {
            Ok(Some(0))
        }
    } else {
        Ok(Some(0))
    }
}

// see https://www.sea-ql.org/SeaORM/docs/advanced-query/custom-joins/
pub async fn get_sales_top10(
    db: &DatabaseConnection,
    begin: Option<NaiveDateTime>,
    end: Option<NaiveDateTime>,
) -> Result<Vec<GoodsSalesDTO>, DbErr> {
    let mut stmt = order_detail::Entity::find()
        .select_only()
        .join_rev(
            JoinType::InnerJoin,
            entities::orders::Entity::belongs_to(order_detail::Entity)
                .from(entities::orders::Column::Id)
                .to(entities::order_detail::Column::OrderId)
                .into(),
        )
        .column_as(entities::order_detail::Column::Name, "name")
        .column_as(entities::order_detail::Column::Number.sum(), "number")
        .filter(entities::orders::Column::Status.eq(constant::order::COMPLETED));

    if !begin.is_none() {
        stmt = stmt.filter(entities::orders::Column::OrderTime.gte(begin));
    }

    if !end.is_none() {
        stmt = stmt.filter(entities::orders::Column::OrderTime.lte(end));
    }

    stmt.group_by(entities::order_detail::Column::Name)
        .order_by_desc(entities::order_detail::Column::Number.sum())
        .limit(10)
        .into_model::<GoodsSalesDTO>()
        .all(db)
        .await
}
